package com.dy.dao;

import com.dy.bean.Sugar;
import com.dy.bean.SugarTwo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT a.*,RANK() over(PARTITION by a.name ORDER BY a.num) FROM\n" +
            "(SELECT b.org_name 'name',\n" +
            "sum(actual_distance) 'distance',\n" +
            "count(*) 'Num',\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))'time',\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(*) 'avgTime',\n" +
            "sum(actual_distance)/count(*)'avgNum'\n" +
            "FROM transport_task t \n" +
            "LEFT JOIN line_base_info l on t.line_id = l.id \n" +
            "LEFT JOIN base_organ b on l.org_id = b.id \n" +
            "GROUP BY b.org_name)a")
    List<SugarTwo> showJg();
    @Select("SELECT sum(amount)'value' FROM order_info WHERE SUBSTRING(create_time,1,10) = '2023-01-06'")
    Double showMoney();
    @Select("call sp8")
    List<Sugar> showHw();
    @Select("SELECT b.name 'name',count(*)'value' FROM order_info o \n" +
            "LEFT JOIN order_cargo c on o.id =c.order_id\n" +
            "LEFT JOIN base_dic b on b.id = c.cargo_type\n" +
            "GROUP BY b.name")
    List<Sugar> showHw2();
    @Select("SELECT r2.name,count(*)'VALUE' FROM express_task_delivery e \n" +
            "LEFT JOIN base_organ b on e.org_id = b.id \n" +
            "LEFT JOIN base_region_info r on b.region_id = r.id\n" +
            "LEFT JOIN base_region_info r2 on r.parent_id = r2.id\n" +
            "GROUP BY r2.name")
    List<Sugar> showCity();
    @Select("SELECT '运输次数' name,count(*)'value' FROM transport_task WHERE SUBSTRING(create_time,1,10) = '2023-01-06'\n" +
            "UNION\n" +
            "SELECT '完成运输里程' name,sum(actual_distance)'value' FROM transport_task WHERE SUBSTRING(create_time,1,10) = '2023-01-06'\n" +
            "UNION\n" +
            "SELECT '完成运输时长' name,sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))'value' FROM transport_task WHERE SUBSTRING(create_time,1,10) = '2023-01-06'")
    List<Sugar> showSum();
    @Select("SELECT count(*) FROM transport_task WHERE SUBSTRING(create_time,1,10) = '2023-01-06'")
    Double showDaySum();
    @Select("SELECT r2.name 'name' ,count(sorter_emp_id)'value'\n" +
            "FROM transport_task_detail d \n" +
            "LEFT JOIN transport_task t on d.transport_task_id = t.id \n" +
            "LEFT JOIN line_base_info l on t.line_id = l.id \n" +
            "LEFT JOIN base_organ b on b.id = l.org_id\n" +
            "LEFT JOIN base_region_info r on b.region_id = r.id \n" +
            "LEFT JOIN base_region_info r2 on r.parent_id = r2.id\n" +
            "GROUP BY r2.name")
    List<Sugar> showMap();
}
